"""
Case Type   : 基础功能
Case Name   : analyze使gpc失效清理
Create At   : 2025/06/09
Owner       : liu-tong-8848
Description :
    1.开启GPC
    2.重启数据库
    3.创建表
    4.创建prepare语句
    5.调用p1_1
    6.查询计划
    7.analyze
    8.查询计划
Expect      :
History     :
"""
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant


class Gpcclass(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info("-----------this is setup-----------")
        self.log.info("Opengauss_Function_GPC_case0006 start")
        self.constant = Constant()
        self.commonshpri = CommonSH('PrimaryDbUser')
        self.tb_name = "gpc_tb"
        self.pre_name = "gpc_tb_pre"
        self.idx_name = "gpc_idx"

        result = self.commonshpri.execut_db_sql('show enable_thread_pool;')
        self.log.info(f"enable_thread_pool is {result}")
        self.enable_thread_pool = result.strip().splitlines()[-2]

        result = self.commonshpri.execut_db_sql(
            'show enable_pbe_optimization')
        self.log.info(f"enable_pbe_optimization is {result}")
        self.enable_pbe_optimization = result.strip().splitlines()[-2]

        result = self.commonshpri.execut_db_sql(
            'show enable_global_plancache;')
        self.log.info(f"enable_global_plancache is {result}")
        self.enable_global_plancache = result.strip().splitlines()[-2]

    def test_index(self):
        self.log.info('-------------1.开启GPC-----------')
        result = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG, "enable_thread_pool = on")
        self.assertTrue(result)
        result = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG, "enable_pbe_optimization = on")
        self.assertTrue(result)
        result = self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            "enable_global_plancache = on")
        self.assertTrue(result)

        self.log.info('--------------2.重启数据库------------------')
        result = self.commonshpri.restart_db_cluster()
        self.assertTrue(result)

        self.log.info("---------------3.创建表----------------------")
        cmd = f"drop table if exists {self.tb_name};" \
            f"create table {self.tb_name}(i int);" \
            f"create index {self.idx_name} on {self.tb_name}(i);" \
            f"insert into {self.tb_name} values(generate_series(1,10900));"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result)
        self.assertIn(self.constant.CREATE_INDEX_SUCCESS_MSG, result)

        self.log.info("------------4.创建prepare语句5.执行prepare-------------")
        cmd = f"prepare {self.pre_name}(int) as " \
            f"select count(*) from {self.tb_name} where i>\$1;" \
            f"execute {self.pre_name}(10);"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn(self.constant.PREPARE_SUCCESS_MSG, result)
        self.assertIn('10890', result)

        self.log.info("-----------6.查询计划------------------")
        cmd = f"select * from dbe_perf.global_plancache_status " \
            f"where query='prepare {self.pre_name}(int) " \
            f"as select count(*) from gpc_tb where i>\$1;' " \
            f"and valid = 't';"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn('(1 row)', result)

        self.log.info("-----------7.analyze tb------------------")
        cmd = f"analyze {self.tb_name};"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn(self.constant.ANALYZE_SUCCESS_MSG, result)

        self.log.info("-----------8.查询计划------------------")
        cmd = f"select * from dbe_perf.global_plancache_status; "
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn('(0 rows)', result)

        self.log.info("------------9.创建prepare语句5.执行prepare-------------")
        cmd = f"prepare {self.pre_name}(int) as " \
            f"select count(*) from {self.tb_name} where i>\$1;" \
            f"execute {self.pre_name}(10);" \
            f"select * from dbe_perf.global_plancache_status;"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn(self.constant.PREPARE_SUCCESS_MSG, result)
        self.assertIn('10890', result)

        self.log.info("-----------10.analyze idx------------------")
        cmd = f"analyze verify COMPLETE {self.idx_name};"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn(self.constant.ANALYZE_SUCCESS_MSG, result)

        self.log.info("-----------11.查询计划------------------")
        cmd = f"select * from dbe_perf.global_plancache_status ; "
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)
        self.assertIn('(1 row)', result)

    def tearDown(self):
        self.log.info('------------this is tearDown-------------')
        self.log.info('------------------清理环境-------------')
        cmd = f"drop table if exists {self.tb_name} cascade;"
        result = self.commonshpri.execut_db_sql(cmd)
        self.log.info(result)

        self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_thread_pool = {self.enable_thread_pool}")
        self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_pbe_optimization = {self.enable_pbe_optimization}")
        self.commonshpri.execute_gsguc(
            "set", self.constant.GSGUC_SUCCESS_MSG,
            f"enable_global_plancache = {self.enable_global_plancache}")

        self.commonshpri.restart_db_cluster()
        self.log.info("-Opengauss_Function_GPC_case0006 end-")
